/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import java.io.IOException;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
/**
 *
 * @author Martin Spencer Sy
 */
@WebServlet(name="transactionmanager", urlPatterns={"/TransactionManager"})
public class TransactionManager extends HttpServlet {
    private static Connection con = null;
    private static PreparedStatement ps = null;
    private static String sql;
    
    public static int purchaseProduct(String password, HttpSession session) throws ServletException, IOException {
        int user = -1,
                buyResult = 0;
        String product = null,
                username = null;
        ResultSet rs = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            username = (String)session.getAttribute("name");
            
            sql = "call loginUser(?, ?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            
            if( rs.next() ){
                user = Integer.parseInt( (String)session.getAttribute("user") );    //get the user id of the currently logged in user
                product = (String)session.getAttribute("buy-product");

                sql = "CALL buyProduct(?, ?)";
                ps = con.prepareStatement(sql);
                ps.setInt(1, user);
                ps.setString(2, product); 
                buyResult = ps.executeUpdate();

                if(buyResult == 1){
                    closeDB();
                    return 1;
                }
                else{
                    closeDB();
                    return -1;
                }
            }
            else{
                return 0;
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
            return -1;
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
            return -1;
        }
        catch (NullPointerException e1){
            return -1;
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
            return -1;
        }
    }
    
    public static int getPurchasedProduct(String product) throws ServletException, IOException {
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT pid FROM listProduct WHERE pindex=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, product);
            
            rs = ps.executeQuery();
            
            if ( rs.next() ) {
                return rs.getInt(1);
            } else {
                return -1;
            }
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return -1;
    }
    
    private static void closeDB(){
        try {
            con.close();
        } 
        catch (SQLException ex) {
            //Logger.getLogger(ProductManager.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(TransactionManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
    }
}
